﻿/*******************************************************************************
 * Copyright © 2017 FriendStar.Framework 版权所有
 * Author: FZH
 * Description: FriendStar快速开发平台
 * Website：http://www.FriendStar.cn
*********************************************************************************/
using System.Collections.Generic;
using FriendStar.Code;
using System.Data.SqlClient;
using FriendStar.Application.VBaseData;
using System.Text;
using System.Data;
using System;
using System.Linq;

namespace FriendStar.Application.WareHouse
{
    /// <summary>
	/// 设备档案对应的数据应用功能类。
	/// </summary>
	public class ContainerApp
    {
        public object GetSupplierInformations(string useUnit, bool isOut)
        {
            List<Dictionary<string, object>> result = null;

            string conn = "OR IsProduct = '1'";

            string sql = @"SELECT
	                UnitNO,
	                UnitShortName,
	                UnitNO +' '+ UnitShortName UnitNOShortName
                FROM
	                ZSJ_B1_GH_SupplierInformation
                WHERE
	                UseUnit = @UseUnit
                AND (
	                IsCustomer = '1'
	                {0}
                )";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            if (isOut)
            {
                conn = string.Empty;
            }

            sql = string.Format(sql, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetPurchaseInstock(string id)
        {
            throw new NotImplementedException();
        }

        public object GetFlowReceiveAndDispatchStatistics(Pagination pagination, string FAccountDate, string FContainerNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                        FContainerNO,
	                    FContainerName,
	                    JCSL,
	                    QCSL,
	                    RK01,
	                    RK02,
	                    RK03,
	                    RK04,
	                    RK05,
	                    RK06,
	                    RK07,
	                    RK08,
	                    RK09,
	                    RK10,
	                    RK11,
	                    RK12,
	                    RK13,
	                    RK14,
	                    RK15,
	                    RK16,
	                    RK17,
	                    RK18,
	                    RK19,
	                    RK20,
	                    RK21,
	                    RK22,
	                    RK23,
	                    RK24,
	                    RK25,
	                    RK26,
	                    RK27,
	                    RK28,
	                    RK29,
	                    RK30,
	                    RK31,
	                    RKHZ,
	                    RKHZ + QCSL RKHZ_QCSL,
	                    CK01,
	                    CK02,
	                    CK03,
	                    CK04,
	                    CK05,
	                    CK06,
	                    CK07,
	                    CK08,
	                    CK09,
	                    CK10,
	                    CK11,
	                    CK12,
	                    CK13,
	                    CK14,
	                    CK15,
	                    CK16,
	                    CK17,
	                    CK18,
	                    CK19,
	                    CK20,
	                    CK21,
	                    CK22,
	                    CK23,
	                    CK24,
	                    CK25,
	                    CK26,
	                    CK27,
	                    CK28,
	                    CK29,
	                    CK30,
	                    CK31,
	                    CKHZ,
	                    FPSL,
	                    FXSL,
	                    pcsl
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2} and FAccountDate = @FAccountDate", tableName = "V_RQK_33_BB_StoProStockCata_RB";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(FContainerNO))
            {
                conn.Append(string.Format(" and FContainerNO = '{0}'", FContainerNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@FAccountDate", FAccountDate)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public string GetDocumentNum(string useUnit, string factoryFlag, string cDESC, string curUser, string other)
        {
            string result = string.Empty, documentNum = string.Empty;
            // EXEC GetDocumentNum '使用单位','厂标','容器采购','001',@DocumentNum  out ,'临时'
            string sql = " EXEC GetDocumentNum '{0}','{1}','{2}','{3}',@DocumentNum  out ,'{4}' select @DocumentNum ";

            sql = string.Format(sql, useUnit, factoryFlag, cDESC, curUser, other);

            SqlParameter par = new SqlParameter("@DocumentNum", documentNum);

            result = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par).ToString();

            return result;
        }

        public object GetDocuType(string fTypeNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                            FTypeNO,
	                            FTypeName,
                              FTypeNO + ' ' + FTypeName FTypeNOName
                            FROM
	                            BUS_SJ_Dictionary
                            WHERE
	                            FTypeNO = @FTypeNO and useunit = @useunit
                            ORDER BY
	                            FTypeNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useunit", useUnit),
                new SqlParameter("@FTypeNO",fTypeNO)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetContainerTypeNO(string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"
                        SELECT
	                FTypeNO,
				FTypeName,
				FTypeNOName
                FROM
	                V_BUS_SJ_Dictionary
                WHERE
                 UseUnit =@useunit AND FClassNO = '02'";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useunit", useUnit),
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetPrintDetailsData(string useUnit, string curUser, bool isDefaultDate, bool isArchive, string ids)
        {
            List<Dictionary<string, object>> result = null;

            List<Dictionary<string, object>> pageDataList = null;

            DateTime now = DateTime.Now;

            string sql = @" select  isnull(FHappenComName,'') FHappenComName,
	                                isnull(FCredence,'') FCredence,
	                                isnull(FCurDate,'') FCurDate,
	                                isnull(FDescription_DOC,'') FDescription_DOC,
	                                FDocuNO,
	                                FContainerNO,
	                                FContainerName,
	                                FQuantity_KP,
	                                FQuantity,
	                                FPrice,
	                                isnull(FQuantity,0) * isnull(FPrice,0) FAmount,
	                                FStoreName,
	                                FStoreSetNO,
	                                FDescription
		                        FROM
			                        {0}
                where   useUnit = @useUnit  and FDocuNO in({1})", tableName = "V_RQK_12_MX_StoConListAcco";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            pageDataList = new List<Dictionary<string, object>>();

            if (isArchive)
            {
                if (isDefaultDate)
                {
                    tableName = "V_RQK_14_MX_StoConListAcco_Mid";
                }
                else
                {
                    tableName = "V_RQK_16_MX_StoConListAcco_Arc";
                }
            }

            sql = string.Format(sql, tableName, ids);


            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    pageDataList.Add(item);
                }
            }

            var query = pageDataList.GroupBy(entity => entity["FDocuNO"]);

            foreach (IGrouping<object, Dictionary<string, object>> info in query)
            {
                List<Dictionary<string, object>> sl = info.ToList();//分组后的集合 

                Dictionary<string, object> pageItem = new Dictionary<string, object>();

                string[] cols = "FDocuNO,FCredence,FHappenComName,FCurDate,FDescription_DOC".Split(',');

                foreach (string col in cols)
                {
                    pageItem.Add(col, sl[0][col]);
                }

                pageItem.Add("curUser", curUser);

                pageItem.Add("curDate", now.ToString("yyyy-MM-dd"));

                pageItem.Add("curTime", now.ToString("HH:mm:ss"));

                pageItem.Add("dataList", sl);

                result.Add(pageItem);
            }

            return result;
        }

        public object GetPurchaseInstockView(Pagination pagination, string fDocuNO, string useUnit, bool isArchive, bool isDefaultDate, string FDocuTypeNo)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                         FContainerNO,FContainerName,FQuantity_KP,FQuantity,FPrice,FAmount,FDescription,FContainerTypeNO,FContainerTypeName,FStoreNO,FStoreName,FStoreSetNO
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}  and FDocuTypeNo = @FDocuTypeNo", tableName = "V_RQK_12_MX_StoConListAcco";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (isArchive)
            {
                if (isDefaultDate)
                {
                    tableName = "V_RQK_14_MX_StoConListAcco_Mid";
                }
                else
                {
                    tableName = "V_RQK_16_MX_StoConListAcco_Arc";
                }
            }

            if (!string.IsNullOrEmpty(fDocuNO))
            {
                conn.Append(string.Format(" and fDocuNO = '{0}'", fDocuNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@FDocuTypeNo", FDocuTypeNo)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object ExcutePurchaseInstock(Dictionary<string, object> param)
        {
            string documentNum = string.Empty;

            Dictionary<string, object> result = null;

            List<string> conn = new List<string>();

            string sql = " EXEC pro_InOutStoConStockCata {0},@DocNum  out ,@resultState out select @DocNum DocumentNum,@resultState resultState";

            string[] cols = "UseUnit,FAccountDate,FDocuTypeNO,FDocuNO,FCurDate,FHappenComNO,FDocuID,FDocuListID,FContainerNO,FQuantity,FPrice,FStoreNO,FStoreSetNO,FCreateBy,FCredence,FDescription,FDescription2,FFacNum,FItemDesc,DocumentNumType,FDouBarCode,Status".Split(',');

            foreach (string col in cols)
            {
                conn.Add(string.Format("'{0}'", param[col]));
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()));

            SqlParameter[] pars = new SqlParameter[] {
                    new SqlParameter("@resultState", documentNum),
                    new SqlParameter("@DocNum", documentNum)
                };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    result = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }

        public object GetHappenCompanyInfo()
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                            UnitNO,
	                            UnitShortName,
	                            UnitNO + ' ' + UnitShortName UnitShortNOName
                            FROM
	                            ZSJ_B1_GH_SupplierInformation
                            WHERE
	                            (UnitNO = UseUnit)
                            OR IsProduct = '1'
                            OR IsCustomer = '1'
                            ORDER BY
	                            UnitNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object BatchExcutePurchaseInstocks(List<Dictionary<string, object>> paramList)
        {
            string documentNum = string.Empty;

            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> resultItem = null;

            List<string> conn = new List<string>();

            string sql = " EXEC pro_InOutStoConStockCata {0},@DocNum  out ,@resultState out select @DocNum DocumentNum,@resultState resultState";

            string[] cols = "UseUnit,FAccountDate,FDocuTypeNO,FDocuNO,FCurDate,FHappenComNO,FDocuID,FDocuListID,FContainerNO,FQuantity,FPrice,FStoreNO,FStoreSetNO,FCreateBy,FCredence,FDescription,FDescription2,FFacNum,FItemDesc,DocumentNumType,FDouBarCode,Status".Split(',');

            foreach (Dictionary<string, object> param in paramList)
            {
                foreach (string col in cols)
                {
                    conn.Add(string.Format("'{0}'", param[col]));
                }

                sql = string.Format(sql, string.Join(",", conn.ToArray()));

                SqlParameter[] pars = new SqlParameter[] {
                    new SqlParameter("@resultState", documentNum),
                    new SqlParameter("@DocNum", documentNum)
                };

                result = new List<Dictionary<string, object>>();

                using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
                {
                    if (reader.Read())
                    {
                        resultItem = new Dictionary<string, object>();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            resultItem.Add(reader.GetName(i), reader[i]);
                        }

                        result.Add(resultItem);
                    }
                }
            }

            return result;
        }

        public object GetMoveToReceiveAndDispatchStatisticsDetails(Pagination pagination,string FAccountDate, string FHappenCompanyNO, string FContainerNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
	                            *
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY
					                        {4}) num,
			                        b.*
		                FROM
			            (
				        SELECT
					        FDocuNO,
					        FDate,
					        FHappComNO AS FHappenCompanyNO,
					        FHappComName AS FHappenCompanyName,
					        FContainerNO,
					        FContainerName,
					        FQuantity_RK,
					        FQuantity_FX,
					        FQuantity_CK,
					        FQuantity_BS,
					        FQuantity_BR,
					        FQuantity_BC,
					        FQuantity_PC,
					        FStoreSetNO,
					        FQuantity_QC,
				        UseUnit,
				        FAccountDate,
					        CASE FContainerType
				        WHEN '0' THEN
					        '空箱'
				        WHEN '1' THEN
					        '重箱'
				        WHEN '2' THEN
					        '--'
				        END AS ConType,
                        FCreateDate,
                        FCreateTime
				        FROM
					                    {3}
			                    )b
	                    ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2} and FAccountDate = @FAccountDate", tableName = "V_RQK_32_LX_StoConAIAOAcc";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            decimal last = 0, qc = 0, rk = 0, ck = 0, fx = 0, fp = 0, pc = 0, jc = 0;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(FContainerNO))
            {
                conn.Append(string.Format(" and FContainerNO = '{0}'", FContainerNO));
            }
            if (!string.IsNullOrEmpty(FHappenCompanyNO))
            {
                conn.Append(string.Format(" and FHappenCompanyNO = '{0}'", FHappenCompanyNO));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@FAccountDate", FAccountDate)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    qc = 0; rk = 0; ck = 0; fx = 0; fp = 0; pc = 0; jc = 0;

                    if (0 == last)
                    {
                        qc = 0;

                        decimal.TryParse(string.Format("{0}", item["FQuantity_QC"]), out qc);
                    }
                    else
                    {
                        qc = last;
                    }
                    //期末结存 = 期初+入库-出库+返修-废品+盘存
                    //FQuantity_RK	FQuantity_CK	FQuantity_BS	FQuantity_FX	FQuantity_PC
                    decimal.TryParse(string.Format("{0}", item["FQuantity_RK"]), out rk);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_CK"]), out ck);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_BS"]), out fp);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_FX"]), out fx);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_PC"]), out pc);

                    jc = qc + rk - ck + fx - fp + pc;

                    last = jc;

                    item.Add("QC", Convert.ToInt32(qc));

                    item.Add("JC", Convert.ToInt32(jc));

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetMoveToReceiveAndDispatchStatistics(Pagination pagination, string FAccountDate, string FHappenCompanyNO, string FContainerNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
    FAccountDate,
	FHappenCompanyNO,
	FHappenCompanyName,
    FHappenCompanyNO + ' ' +  FHappenCompanyName FHappenCompanyNOName,
	FContainerNO,
	FContainerName,
	ZXKC,
	KXKC,
	KC,
	FQuantity_QC,
	FQuantity_RK,
	FQuantity_CK,
	FQuantity_BS,
	FQuantity_FX,
	FQuantity_PC
FROM
	(
		SELECT
			row_number () OVER (ORDER BY
					{4}) num,
			b.*
		FROM
			(
				SELECT
					FHappenCompanyNO,
					FHappenCompanyName,
					FContainerNO,
					FContainerName,
						MAX (useUnit) useUnit,
						MAX (FAccountDate) FAccountDate,
					SUM (FQuantity_QC) AS FQuantity_QC,
					SUM (FQuantity_RK) AS FQuantity_RK,
					SUM (FQuantity_FX) AS FQuantity_FX,
					SUM (FQuantity_CK) AS FQuantity_CK,
					SUM (FQuantity_BS) AS FQuantity_BS,
					SUM (FQuantity_BR) AS FQuantity_BR,
					SUM (FQuantity_BC) AS FQuantity_BC,
					SUM (FQuantity_PC) AS FQuantity_PC,
					SUM (
						ISNULL(FQuantity_QC, 0) + ISNULL(FQuantity_RK, 0) + ISNULL(FQuantity_FX, 0) - ISNULL(FQuantity_CK, 0) - ISNULL(FQuantity_BS, 0) + ISNULL(FQuantity_PC, 0)
					) AS KC,
					SUM (ISNULL(FQuantity_K, 0)) AS KXKC,
					SUM (
						ISNULL(FQuantity_QC, 0) + ISNULL(FQuantity_RK, 0) + ISNULL(FQuantity_FX, 0) - ISNULL(FQuantity_CK, 0) - ISNULL(FQuantity_BS, 0) - ISNULL(FQuantity_K, 0) + ISNULL(FQuantity_PC, 0)
					) AS ZXKC
				FROM
					{3}
				GROUP BY
					FHappenCompanyNO,
					FHappenCompanyName,
					FContainerNO,
					FContainerName
			)b
	) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2} and FAccountDate = @FAccountDate", tableName = "V_RQK_32_LX_StoConAIAOAcc";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(FContainerNO))
            {
                conn.Append(string.Format(" and FContainerNO = '{0}'", FContainerNO));
            }
            if (!string.IsNullOrEmpty(FHappenCompanyNO))
            {
                conn.Append(string.Format(" and FHappenCompanyNO = '{0}'", FHappenCompanyNO));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@FAccountDate", FAccountDate)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object UpdateContainerDetails(Dictionary<string, object> param)
        {
            string documentNum = string.Empty;

            Dictionary<string, object> result = null;

            List<string> conn = new List<string>();

            string sql = " EXEC pro_InOutStoConStockCata {0},@DocNum  out ,@resultState out select @DocNum DocumentNum,@resultState resultState";

            //UseUnit,
            //FAccountDate,
            //FDocuTypeNO,
            //FDocuNO,
            //FCurDate,
            //FHappenComNO,
            //FDocuID,
            //FDocuListID,
            //FContainerNO,
            //FQuantity,
            //FPrice,
            //FStoreNO,
            //FStoreSetNO,
            //FCreateBy,
            //FCredence,
            //FDescription,
            //FDescription2,
            //FFacNum,
            //FItemDesc,
            //DocumentNumType,
            //FDouBarCode

            string[] cols = "UseUnit,FAccountDate,FDocuTypeNO,FDocuNO,FCurDate,FHappenComNO,FDocuID,FDocuListID,FContainerNO,FQuantity,FPrice,FStoreNO,FStoreSetNO,FCreateBy,FCredence,FDescription,FDescription2,FFacNum,FItemDesc,DocumentNumType,FDouBarCode,Status".Split(',');

            foreach (string col in cols)
            {
                conn.Add(string.Format("'{0}'", param[col]));
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()));

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@resultState", documentNum),
                new SqlParameter("@DocNum", documentNum)
            };

            result = new Dictionary<string, object>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }

        public object GetFStoreSetNOList(string useUnit, string fStoreNO)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"
                        SELECT
	                FStoreSetNO
                FROM
	                RQK_ZD_42_DatStoreHouseSet_RQ
                WHERE
                 UseUnit =@useunit and FStoreNO = @FStoreNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useunit", useUnit),
                new SqlParameter("@FStoreNO", fStoreNO)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetFStoreNOList(string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"
                        SELECT
	                        FStoreNO,
	                        FStoreName,
                            FStoreNO + ' ' + FStoreName FStoreNOName
                        FROM
	                        CPK_ZD_11_DatStoreHouse
                        WHERE
	                        UseUnit  = @useunit
                        AND FStoreNO = 'RQ'
                        ORDER BY
	                        FStoreNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useunit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetContainerNOList(string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                    FContainerNO,
	                    FContainerName,
                        FContainerNO + ' '+ FContainerName FContainerNOName,
	                    FPrice,
	                    FStoreNO,
	                    FStoreName,
	                    FStoreSetNO,
                        FContainerTypeNO,
FContainerTypeName,
FContainerTypeNO+ ' ' + FContainerTypeName FContainerTypeNOName
                    FROM
	                    V_ZSJ_21_RQ_DatContainer_KW
                    WHERE
	                    UseUnit = @useunit";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useunit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }
        public object GetContainerNOOutList(string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                    FContainerNO,
	                    FContainerName,
                        FContainerNO + ' '+ FContainerName FContainerNOName,
	                    FPrice,
	                    FStoreNO,
	                    FStoreName,
	                    FStoreSetNO,
                        FContainerTypeNO,
                        FContainerTypeName,
FContainerTypeNO+ ' ' + FContainerTypeName FContainerTypeNOName,
                        FImmeQuantity,
                        FBookQuantity
                    FROM
	                    V_RQK_21_KC_StoConStockCata_KW
                    WHERE
	                    UseUnit = @useunit";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useunit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }
        public object GetFlowReceiveAndDispatchExchange(Pagination pagination, string fHappenCompanyNO, string FContainerNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                        FHappenCompanyNO,
	                    FHappenCompanyName,
	                    FContainerTypeNO,
	                    FContainerTypeName,
	                    FContainerNO,
	                    FContainerName,
	                    KCZS,
	                    QC,
	                    KRK,
	                    KCK,
	                    KBS,
	                    KFX,
	                    KPC,
	                    KKC,
	                    ZRK,
	                    ZCK,
	                    ZKC
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_32_LX_StoConAIAOAcc_ZKHZ";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fHappenCompanyNO))
            {
                conn.Append(string.Format(" and FHappenCompanyNO = '{0}'", fHappenCompanyNO));
            }

            if (!string.IsNullOrEmpty(FContainerNO))
            {
                conn.Append(string.Format(" and FContainerNO = '{0}'", FContainerNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetContainerLedgerDetails(Pagination pagination, string fAccountDate, string useUnit, string fContainerNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                                FDocuNO,
	                            FCredence,
	                            FStoreSetNO,
	                            FPrice,
	                            FAmount,
	                            CASE FDocuTypeNO
                            WHEN 'QC' THEN
	                            '期初'
                            WHEN 'RG' THEN
	                            '采购入库'
                            WHEN 'RR' THEN
	                            '周转入库'
                            WHEN 'RI' THEN
	                            '调拨入库'
                            WHEN 'RC' THEN
	                            '出库'
                            WHEN 'RO' THEN
	                            '调拨出库'
                            WHEN 'RF' THEN
	                            '返修'
                            WHEN 'RS' THEN
	                            '报废'
                            WHEN 'RP' THEN
	                            '盘存'
                            END AS ZY,
                             FCurDate,
                             FQuantity_QC,
                             FQuantity_RK,
                             FQuantity_CK,
                             FQuantity_FX,
                             FQuantity_BS,
                             FQuantity_PC,
                             FHappenComNO,
                             FHappenComName,
                             FDescription
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_14_MX_StoConListAcco_Mid";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            decimal last = 0, qc = 0, rk = 0, ck = 0, fx = 0, fp = 0, pc = 0, jc = 0, a = 0, p = 0;

            if (!isDefaultDate)
            {
                tableName = "V_RQK_16_MX_StoConListAcco_Arc";
            }

            if (!isDefaultDate && !string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fContainerNO))
            {
                conn.Append(string.Format(" and fContainerNO = '{0}'", fContainerNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    qc = 0; rk = 0; ck = 0; fx = 0; fp = 0; pc = 0; jc = 0;

                    if (0 == last)
                    {
                        qc = 0;

                        decimal.TryParse(string.Format("{0}", item["FQuantity_QC"]), out qc);
                    }
                    else
                    {
                        qc = last;
                    }
                    //期末结存 = 期初+入库-出库+返修-废品+盘存
                    //FQuantity_RK	FQuantity_CK	FQuantity_BS	FQuantity_FX	FQuantity_PC
                    decimal.TryParse(string.Format("{0}", item["FQuantity_RK"]), out rk);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_CK"]), out ck);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_BS"]), out fp);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_FX"]), out fx);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_PC"]), out pc);

                    decimal.TryParse(string.Format("{0}", item["FPrice"]), out p);

                    jc = qc + rk - ck + fx - fp + pc;

                    a = p * jc;

                    last = jc;

                    item.Add("QC", Convert.ToInt32(qc));

                    item["FAmount"] = a;

                    item.Add("JC", Convert.ToInt32(jc));

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetReceiveAndDispatchStatisticsParts(Pagination pagination, string fAccountDate, string useUnit, string fContainerTypeNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                     FContainerNO,
	                FContainerName,
	                FContainerTypeNO,
	                FContainerTypeName,
	                FQuantity_QC,
	                FMoney_QC,
	                FQuantity_RK,
	                FMoney_RK,
	                FQuantity_CK,
	                FMoney_CK,
	                FQuantity_BS,
	                FMoney_BS,
	                FQuantity_FX,
	                FMoney_FX,
	                FQuantity_PC,
	                FMoney_PC,
	                FQuantity_KC,
	                FMoney_KC
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_14_MX_StoConListAcco_Mid_LJ";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_RQK_16_MX_StoConListAcco_Arc_LJ";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fContainerTypeNO))
            {
                conn.Append(string.Format(" and fContainerTypeNO = '{0}'", fContainerTypeNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetReceiveAndDispatchStatisticsCate(Pagination pagination, string fAccountDate, string useUnit, string fContainerTypeNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                        FContainerTypeNO,
	                    FContainerTypeName,
	                    FQuantity_QC,
	                    FMoney_QC,
	                    FQuantity_RK,
	                    FMoney_RK,
	                    FQuantity_CK,
	                    FMoney_CK,
	                    FQuantity_BS,
	                    FMoney_BS,
	                    FQuantity_FX,
	                    FMoney_FX,
	                    FQuantity_PC,
	                    FMoney_PC,
	                    FQuantity_KC,
	                    FMoney_KC
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_14_MX_StoConListAcco_Mid_Type";
            

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_RQK_16_MX_StoConListAcco_Arc_Type";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fContainerTypeNO))
            {
                conn.Append(string.Format(" and fContainerTypeNO = '{0}'", fContainerTypeNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object CalcStoProStockCataSum(string date, bool isCurrent, string useunit, string userName)
        {
            throw new NotImplementedException();
        }

        public object GetContainerLedgerStatistics(Pagination pagination, string fAccountDate, string useUnit, string fContainerNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                    *
	                    FROM
		                    (
			                    SELECT
				                    row_number () OVER (

					                    ORDER BY {4}
				                    ) num,
				                    o.*
			                    FROM
				                    (SELECT
	                    FCurDate,
	                    FHappenComNO,
	                    FHappenComName,
	                    SUM (FQuantity_QC) AS FQuantity_QC,
	                    SUM (FQuantity_RK) AS FQuantity_RK,
	                    SUM (FQuantity_CK) AS FQuantity_CK,
	                    SUM (FQuantity_FX) AS FQuantity_FX,
	                    SUM (FQuantity_BS) AS FQuantity_BS,
	                    SUM (FQuantity_BR) AS FQuantity_BR,
	                    SUM (FQuantity_BC) AS FQuantity_BC,
	                    SUM (FQuantity_PC) AS FQuantity_PC,
	                    max(UseUnit) as UseUnit,
	                    max(FContainerNO) as FContainerNO,
                        max(FDocuNO) as FDocuNO,
                        max(Fcredence) as Fcredence,
                         avg(FPrice) as FPrice
                    FROM
	                    {3}
                    GROUP BY
	                    FCurDate,
	                    FHappenComNO,
	                    FHappenComName ) o
		                    ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_14_MX_StoConListAcco_Mid";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            decimal last = 0, qc = 0, rk = 0, ck = 0, fx = 0, fp = 0, pc = 0, jc = 0, a = 0, p = 0;

            if (!isDefaultDate)
            {
                tableName = "V_RQK_16_MX_StoConListAcco_Arc";
            }

            if (!isDefaultDate && !string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fContainerNO))
            {
                conn.Append(string.Format(" and fContainerNO = '{0}'", fContainerNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    qc = 0; rk = 0; ck = 0; fx = 0; fp = 0; pc = 0; jc = 0;

                    if (0 == last)
                    {
                        qc = 0;

                        decimal.TryParse(string.Format("{0}", item["FQuantity_QC"]), out qc);
                    }
                    else
                    {
                        qc = last;
                    }
                    //期末结存 = 期初+入库-出库+返修-废品+盘存
                    //FQuantity_RK	FQuantity_CK	FQuantity_BS	FQuantity_FX	FQuantity_PC
                    decimal.TryParse(string.Format("{0}", item["FQuantity_RK"]), out rk);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_CK"]), out ck);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_BS"]), out fp);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_FX"]), out fx);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_PC"]), out pc);

                    decimal.TryParse(string.Format("{0}", item["FPrice"]), out p);

                    jc = qc + rk - ck + fx - fp + pc;

                    a = p * jc;

                    last = jc;

                    item.Add("QC", Convert.ToInt32(qc));

                    item.Add("FAmount",a);

                    item.Add("JC", Convert.ToInt32(jc));

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetContainerLedgers(Pagination pagination, string fAccountDate, string useUnit, string fContainerNO, string fContainerTypeNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                       FContainerNO,
	FContainerName,
	FImmeQuantity,
	FBookQuantity,
	isnull(FPrice, 0) FPrice,
	FBookMoney,
	FContainerTypeNO,
	FContainerTypeName,
	isnull(FHighestStore, 0) FHighestStore,
	isnull(FLowestStore, 0) FLowestStore
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_21_KC_StoConStockCata";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!isDefaultDate)
            {
                tableName = "V_RQK_22_KC_StoConStockCata_Arc";
            }

            if (!isDefaultDate && !string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fContainerNO))
            {
                conn.Append(string.Format(" and fContainerNO like '%{0}%'", fContainerNO));
            }

            if (!string.IsNullOrEmpty(fContainerTypeNO))
            {
                conn.Append(string.Format(" and fContainerTypeNO = '{0}'", fContainerTypeNO));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetPurchaseInstockAdds(Pagination pagination, string fContainerNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                       FContainerNO,
	                    FContainerName,
	                    FPrice,
	                    FStoreNO,
	                    FStoreName,
	                    FStoreSetNO,
FContainerTypeNO,
FContainerTypeName,
FContainerTypeNO+ ' ' + FContainerTypeName FContainerTypeNOName

                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_ZSJ_21_RQ_DatContainer_KW";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fContainerNO))
            {
                conn.Append(string.Format(" and fContainerNO like '%{0}%'", fContainerNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }
        public object GetPurchaseOutstockAdds(Pagination pagination, string fContainerNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                       FContainerNO,
	                    FContainerName,
	                    FPrice,
	                    FStoreNO,
	                    FStoreName,
	                    FStoreSetNO,
FContainerTypeNO,
FContainerTypeName,
FImmeQuantity,
FBookQuantity
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_RQK_21_KC_StoConStockCata_KW";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fContainerNO))
            {
                conn.Append(string.Format(" and fContainerNO like '%{0}%'", fContainerNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }
        public object GetPurchaseInstocks(Pagination pagination, string fDocuNO, string fHappenComNO, string fContainerNO, string fCurDate, string fcredence, string fAccountDate, string useUnit, bool isDefaultDate, bool isArchive, string FDocuTypeNo)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                        FDocuListID,
                        FDocuTypeNo,
	                    FDocuTypeName,
	                    FDocuNO,
	                    FCurDate,
	                    FHappenComNO,
	                    FHappenComName,
	                    FContainerNO,
	                    FContainerName,
	                    FQuantity_KP,
	                    FQuantity,
	                    FPrice,
	                   isnull(FQuantity,0) * isnull(FPrice,0) FAmount,
	                    FContainerTypeNO,
	                    FContainerTypeName,
	                    FCredence,
	                    FDescription,
	                    FCreateBy,
	                    FCreateDate,
	                    FCreateTime,
	                    FAuditingBy,
	                    FAuditingDate,
	                    FAuditingTime,
                        FStoreNO,
                         FStoreSetNO,
                        FDocuID,
FAccountDate,
FDescription_DOC
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2} and FDocuTypeNo = @FDocuTypeNo", tableName = "V_RQK_12_MX_StoConListAcco";



            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (isArchive)
            {
                if (isDefaultDate)
                {
                    tableName = "V_RQK_14_MX_StoConListAcco_Mid";
                }
                else
                {
                    tableName = "V_RQK_16_MX_StoConListAcco_Arc";
                }
            }

            if (!string.IsNullOrEmpty(fDocuNO))
            {
                conn.Append(string.Format(" and fDocuNO = '{0}'", fDocuNO));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and fHappenComNO = '{0}'", fHappenComNO));
            }
            if (!string.IsNullOrEmpty(fContainerNO))
            {
                conn.Append(string.Format(" and fContainerNO = '{0}'", fContainerNO));
            }

            if (!string.IsNullOrEmpty(fCurDate))
            {
                conn.Append(string.Format(" and fCurDate = '{0}'", fCurDate));
            }

            if (!string.IsNullOrEmpty(fcredence))
            {
                conn.Append(string.Format(" and fcredence = '{0}'", fcredence));
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@FDocuTypeNo", FDocuTypeNo)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public string GetLedgerDate(string useUnit, string facNum, string itemDesc, string createBy)
        {
            string result = string.Empty, documentNum = string.Empty;

            string sql = "exec GetDocumentNum '{0}','{1}','{2}','{3}',@DocumentNum out,'临时';select @DocumentNum;";

            sql = string.Format(sql, useUnit, facNum, itemDesc, createBy);

            SqlParameter par = new SqlParameter("@DocumentNum", documentNum);

            result = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par).ToString();

            return result;
        }

        public object BatchExcuteAuditContainer(List<Dictionary<string, object>> paramList)
        {
            string documentNum = string.Empty;

            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> resultItem = null;

            List<string> conn = new List<string>();

            string sql = " EXEC pro_InOutStoConBookStorage {0}";
            //UseUnit FCreateBy Status DocumetStatus FDocuNO FAccountDate
            string[] cols = "UseUnit,FCreateBy,Status,DocumetStatus,FDocuNO,FAccountDate".Split(',');

            foreach (Dictionary<string, object> param in paramList)
            {
                foreach (string col in cols)
                {
                    conn.Add(string.Format("'{0}'", param[col]));
                }

                sql = string.Format(sql, string.Join(",", conn.ToArray()));

                result = new List<Dictionary<string, object>>();

                using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
                {
                    if (reader.Read())
                    {
                        resultItem = new Dictionary<string, object>();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            resultItem.Add(reader.GetName(i), reader[i]);
                        }

                        result.Add(resultItem);
                    }
                }
            }

            return result;
        }
    }

    public enum 采购状态
    {
        出库新增 = 11, 出库修改 = 12, 出库删除 = 13, 入库新增 = 21, 入库修改 = 22, 入库删除 = 23, 返修新增 = 31, 返修修改 = 32, 返修删除 = 33, 盘存新增 = 41, 盘存修改 = 42, 盘存删除 = 43, 报废新增 = 51, 报废修改 = 52, 报废删除 = 53
    }
}




